package com.xjgc.ws.eip.dao;

import com.xjgc.common.config.DbConn;
import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.time.Instant;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;

/*
 * EIP销售订单接口
 */
@Component
public class ZxjEip2cVbeln {

	@Autowired
	DbConn db;

	private String data;

	/**
	 * 根据销售组织进行判断，执行以下逻辑
	 * @param xmlData
	 * @return
	 * @throws SQLException
	 */
	public String vbelnJudgeBySupplier(String xmlData) throws SQLException {
		this.data = xmlData;
		Document document = null;
		String res = "";
		try {
			document = DocumentHelper.parseText(xmlData);
			Element root = document.getRootElement();
			Iterator<?> iterator = root.elementIterator();
			while (iterator.hasNext()) {
				Element element = (Element) iterator.next();
				String vkorg = element.elementText("VKORG");//销售组织编码
				if ("920A".equals(vkorg)) { //销售组织为保自
					res =  vbeln_old(element);
				} else if ("9212".equals(vkorg) || "9251".equals(vkorg)) {//销售组织为许继仪表和山东电子
					res =  vbeln_new(element);
				}
			}

		} catch (DocumentException e) {
			e.printStackTrace();
		}
		return res;
	}

	public String vbeln_new(Element element) throws SQLException{
		String rs = null;
		PreparedStatement inPst = null;
		PreparedStatement uptPst = null;

		// 定义执行SQL脚本
		String inSql = "INSERT INTO zxj_eip_xsdd(PURCHASERHQCODE, SONO, SUPPLIERCODE, BUYERCODE, BUYERPROVINCE, SORELTYPE, CATEGORYCODE, SUBCLASSCODE, SOSTATUS, DATASOURCE, " +
				"DATASOURCECREATETIME, ISDELETED, SUPPLIERSONO, SOLTEMNO, SUPPLIERSORELTYPE, PRODUCTCODE, PRODUCTNAME, PRODUCTUNIT, PRODUCTAMOUNT, PRODUCTIDGRPNO, PLANDELIVERYDATE, " +
				"PLANDELIVERYPLACE, PLANDELIVERYPLACEDESC, IOFLAG, MESSAGE, STATUS, TRANSNO, EIPPONO, OWNERID, OPENID) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; // 31插入SQL
		String uptSql = "update zxj_eip_xsdd set PURCHASERHQCODE, SUPPLIERCODE, BUYERCODE, BUYERPROVINCE, SORELTYPE, CATEGORYCODE, SUBCLASSCODE, SOSTATUS, DATASOURCE, DATASOURCECREATETIME, " +
				"ISDELETED, SUPPLIERSONO, SUPPLIERSORELTYPE, PRODUCTCODE, PRODUCTNAME, PRODUCTUNIT, PRODUCTAMOUNT, PRODUCTIDGRPNO, PLANDELIVERYDATE, PLANDELIVERYPLACE, " +
				"PLANDELIVERYPLACEDESC, IOFLAG, MESSAGE, STATUS, TRANSNO, ORDER_ID, EIPPONO, OWNERID, OPENID  where SONO=? and SOLTEMNO = ?"; // 16插入SQL
		// 创建数据库连接
		inPst = db.getConnetion().prepareStatement(inSql);
		uptPst = db.getConnetion().prepareStatement(uptSql);
		int count = 0;
		// 解析xml文件
		String eip1 = ""; // 上一条eip关键字段组合数据
		try {

			String PURCHASERHQCODE = "sgcc";//采购方总部编码
			String SONO = element.elementText("VBELN");//销售订单号
			//        代工逻辑判断
			String vkorg = element.elementText("VKORG");
			String bstkd_e = element.elementText("BSTKD_E");
			String SUPPLIERCODE = "";
			if ("@".equals(bstkd_e.indexOf(0))){//代工订单
				SUPPLIERCODE = vkorg;
			}else if("#".equals(bstkd_e.indexOf(0))){//正常订单
				if("9212".equals(vkorg)){//山东电子
					SUPPLIERCODE = "1000008696";
				}else if("9251".equals(vkorg)){//许继仪表
					SUPPLIERCODE = "1000069096";
				}
			}
			String BUYERCODE = "";//采购方公司编码
			String BUYERPROVINCE = "";//采购方公司省份
			String SORELTYPE = "0";//销售订单与采购订单的关联方式
			String CATEGORYCODE = "25";//品类编码 25
			String SUBCLASSCODE = "01";//种类编码 01
			String SOSTATUS = element.elementText("ABSTK");
			String DATASOURCE = "0";
			String DATASOURCECREATETIME = "";
			String ISDELETED = "";//删除标志
			String SUPPLIERSONO = element.elementText("VBELN");//供应商销售订单号
			String SOLTEMNO = element.elementText("POSNR");//销售订单行项目号
			String SUPPLIERSORELTYPE = "0";//销售订单行项目与采购订单的关联方式
			String PRODUCTCODE =element.elementText("MATNR");//物资编码
			String PRODUCTNAME = element.elementText("ARKTX");//物资名称
			String PRODUCTUNIT = element.elementText("VRKME");//物资单位
			String PRODUCTAMOUNT = element.elementText("KWMENG");//物资数量
			String PRODUCTIDGRPNO = "";//物资id分组编号
			String PLANDELIVERYDATE = element.elementText("VDATU");//采购订单厂家计划交货日期
			String PLANDELIVERYPLACE = element.elementText("VSTEL");//采购订单厂家发货地点
			String PLANDELIVERYPLACEDESC = element.elementText("ZFHDD");//采购订单厂家发货地点描述
			String IOFLAG = "1";
			String MESSAGE = "";
			String STATUS = "";
			String TRANSNO = "";
			String EIPPONO = "";
			String OWNERID = "1";
			String OPENID = "1";

			String AESKD = element.elementText("AESKD");//判断是否是国网项目
			String eip2 = PURCHASERHQCODE + SONO + SUPPLIERCODE + BUYERCODE + BUYERPROVINCE + SORELTYPE + CATEGORYCODE + SUBCLASSCODE + SOSTATUS + DATASOURCE + DATASOURCECREATETIME +ISDELETED + SUPPLIERSONO + SOLTEMNO +
					SUPPLIERSORELTYPE + PRODUCTCODE + PRODUCTNAME + PRODUCTUNIT + PRODUCTAMOUNT + PRODUCTIDGRPNO + PLANDELIVERYDATE+ PLANDELIVERYPLACE+PLANDELIVERYPLACEDESC+IOFLAG+MESSAGE+STATUS+TRANSNO+
					EIPPONO+OWNERID+OPENID;
			if (eip1==eip2||eip1.equals(eip2)) {// 比对上下两条不一致，执行插入判断逻辑
			}else {
				if (isSGxm_xsdd(AESKD)) {// 是否为国网订单
					if (queryData_xsdd(SONO)) {// true 表中无此数据需要插入
						inPst.setString(1, PURCHASERHQCODE);
						inPst.setString(2, SONO);
						inPst.setString(3, SUPPLIERCODE);
						inPst.setString(4, BUYERCODE);
						inPst.setString(5, BUYERPROVINCE);
						inPst.setString(6, SORELTYPE);
						inPst.setString(7, CATEGORYCODE);
						inPst.setString(8, SUBCLASSCODE);
						inPst.setString(9, SOSTATUS);
						inPst.setString(10, DATASOURCE);
						inPst.setString(11, DATASOURCECREATETIME);
						inPst.setString(12, ISDELETED);
						inPst.setString(13, SUPPLIERSONO);
						inPst.setString(14, SOLTEMNO);
						inPst.setString(15, SUPPLIERSORELTYPE);
						inPst.setString(16, PRODUCTCODE);
						inPst.setString(17, PRODUCTNAME);
						inPst.setString(18, PRODUCTUNIT);
						inPst.setString(19, PRODUCTAMOUNT);
						inPst.setString(20, PRODUCTIDGRPNO);
						inPst.setString(21, PLANDELIVERYDATE);
						inPst.setString(22, PLANDELIVERYPLACE);
						inPst.setString(23, PLANDELIVERYPLACEDESC);
						inPst.setString(24, IOFLAG);
						inPst.setString(25, MESSAGE);
						inPst.setString(26, STATUS);
						inPst.setString(27, TRANSNO);
						inPst.setString(28, EIPPONO);
						inPst.setString(29, OWNERID);
						inPst.setString(30, OPENID);


						// 插入更新数据
						int row = inPst.executeUpdate();
						if (row > 0) {
							count++;
						}
					}else {//false  表中有此数据需要修改

						uptPst.setString(1, PURCHASERHQCODE);
						uptPst.setString(2, SONO);
						uptPst.setString(3, SUPPLIERCODE);
						uptPst.setString(4, BUYERCODE);
						uptPst.setString(5, BUYERPROVINCE);
						uptPst.setString(6, SORELTYPE);
						uptPst.setString(7, CATEGORYCODE);
						uptPst.setString(8, SUBCLASSCODE);
						uptPst.setString(9, SOSTATUS);
						uptPst.setString(10, DATASOURCE);
						uptPst.setString(11, DATASOURCECREATETIME);
						uptPst.setString(12, ISDELETED);
						uptPst.setString(13, SUPPLIERSONO);
						uptPst.setString(14, SOLTEMNO);
						uptPst.setString(15, SUPPLIERSORELTYPE);
						uptPst.setString(16, PRODUCTCODE);
						uptPst.setString(17, PRODUCTNAME);
						uptPst.setString(18, PRODUCTUNIT);
						uptPst.setString(19, PRODUCTAMOUNT);
						uptPst.setString(20, PRODUCTIDGRPNO);
						uptPst.setString(21, PLANDELIVERYDATE);
						uptPst.setString(22, PLANDELIVERYPLACE);
						uptPst.setString(23, PLANDELIVERYPLACEDESC);
						uptPst.setString(24, IOFLAG);
						uptPst.setString(25, MESSAGE);
						uptPst.setString(26, STATUS);
						uptPst.setString(27, TRANSNO);
						uptPst.setString(28, EIPPONO);
						uptPst.setString(29, OWNERID);
						uptPst.setString(30, OPENID);


						// 插入更新数据
						int row = uptPst.executeUpdate();
						if (row > 0) {
							count++;
						}
					}
				}
				eip1 = eip2;
			}
			if (count > 0) {
				rs = "0";// 执行成功
			}
		} finally {
			// 关闭连接
			inPst.close();
			db.closeCn();
		}
		return rs;
	}

	public String vbeln_old(Element element) throws SQLException {
		String rs = null;
		PreparedStatement inPst = null;
		PreparedStatement uptPst = null;

		// 定义执行SQL脚本
		String inSql = "INSERT INTO zxj_eip_2cvbeln(no,customerorderno,customername,suppliername,projectname,orderdate,demanddate,shipmentdate,status,deliverystatus,tags,labels,ioflag,bstkd,categorycode,mstatus) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; // 16插入SQL
		String uptSql = "update zxj_eip_2cvbeln set customerorderno=?,customername=?,suppliername=?,projectname=?,orderdate=?,demanddate=?,shipmentdate=?,status=?,deliverystatus=?,tags=?,labels=?,ioflag=?,bstkd=?,categorycode=?,mstatus=? where no=?"; // 16插入SQL
		// 创建数据库连接
		inPst = db.getConnetion().prepareStatement(inSql);
		uptPst = db.getConnetion().prepareStatement(uptSql);
		int count = 0;
		// 解析xml文件
		String eip1 = ""; // 上一条eip关键字段组合数据
		try {
				String vbeln = element.elementText("VBELN");
				String bstkde = element.elementText("BSTKD_E");//国网采购订单号
				String bstkd = element.elementText("BSTKD");//销售订单合同号
				String khmc = element.elementText("KHMC");
				String vtext = "许继集团"+element.elementText("VTEXT").replace("销售组织", "");
				String bname = element.elementText("BNAME");
				String s_bstdk = element.elementText("BSTDK");
				if(s_bstdk.equals("00000000")) {
					s_bstdk ="19000101";
				}
				Timestamp bstdk = StringTotimeStamp(s_bstdk);
				String s_vdatu = element.elementText("VDATU");
				if(s_vdatu.equals("00000000")) {
					s_vdatu ="19000101";
				}
				Timestamp vdatu = StringTotimeStamp(s_vdatu);
				String s_wqfdt = element.elementText("WQFDT");
				if(s_wqfdt.equals("00000000")) {
					s_wqfdt ="19000101";
				}
				Timestamp wqfdt = StringTotimeStamp(s_wqfdt);
				String abstk = element.elementText("ABSTK");
				String lfgsk = element.elementText("LFGSK");
				String regio = element.elementText("REGIO");

				String eip2 = vbeln + bstkde + khmc + vtext + bname + bstdk + vdatu + wqfdt + abstk + lfgsk;// 将eip所有必填字段都加上   4000450876

				if (eip1==eip2||eip1.equals(eip2)) {// 比对上下两条不一致，执行插入判断逻辑
				}else {
					if (isSGxm_vbenln(bstkd,khmc,bstkde)) {// 是否为国网订单
						if (queryData_vbeln(vbeln)) {// true 表中无此数据需要插入

							inPst.setString(1, vbeln);// no销售凭证/销售订单编号 Y
							inPst.setString(2, bstkde);// customerorderno国网采购订单编号/客户采购订单标号 Y
							inPst.setString(3, khmc);// customername采购单位/客户名称 Y
							inPst.setString(4, vtext);// suppliername供应商名称/销售组织 -描述 Y
							inPst.setString(5, bname); // projectname项目名称/销售订单项目短文本 Y
							inPst.setTimestamp(6, bstdk);// orderdate订单日期/采购订单日期 Y
							inPst.setTimestamp(7, vdatu);// demanddate需求日期/请求交货日期 Y
							inPst.setTimestamp(8, wqfdt);// shipmentdate发货日期/完全发货日期 Y
							inPst.setString(9, abstk);// status销售订单状态/销售订单状态 Y
							inPst.setString(10, lfgsk);// deliverystatus发货状态/销售订单发货状态 Y
							inPst.setString(11, regio);// tags客户所属省份 ***需截取***
							inPst.setString(12, "");// labels 备用字段
							inPst.setString(13, "1"); // ioflag 代理服务读取状态 0:已读 1:未读
							inPst.setString(14, bstkd); // 销售订单合同号
							inPst.setString(15, "20"); // 销售订单合同号
							inPst.setString(16, "1"); // 新增修改标志
							// 插入更新数据
							int row = inPst.executeUpdate();
							if (row > 0) {
								count++;
							}
						}else {//false  表中有此数据需要修改

							uptPst.setString(1, bstkde);// customerorderno国网采购订单编号/客户采购订单标号 Y
							uptPst.setString(2, khmc);// customername采购单位/客户名称 Y
							uptPst.setString(3, vtext);// suppliername供应商名称/销售组织 -描述 Y
							uptPst.setString(4, bname); // projectname项目名称/销售订单项目短文本 Y
							uptPst.setTimestamp(5, bstdk);// orderdate订单日期/采购订单日期 Y
							uptPst.setTimestamp(6, vdatu);// demanddate需求日期/请求交货日期 Y
							uptPst.setTimestamp(7, wqfdt);// shipmentdate发货日期/完全发货日期 Y
							uptPst.setString(8, abstk);// status销售订单状态/销售订单状态 Y
							uptPst.setString(9, lfgsk);// deliverystatus发货状态/销售订单发货状态 Y
							uptPst.setString(10, regio);// tags客户所属省份 ***需截取***
							uptPst.setString(11, "");// labels 备用字段
							uptPst.setString(12, "1"); // ioflag 代理服务读取状态 0:已读 1:未读
							uptPst.setString(13, bstkd); // 销售订单合同号
							uptPst.setString(14, "20"); // 销售订单合同号
							uptPst.setString(15, "2"); // 新增修改标志
							uptPst.setString(16, vbeln);// no销售凭证/销售订单编号 Y
							// 插入更新数据
							int row = uptPst.executeUpdate();
							if (row > 0) {
								count++;
							}
						}
					}
					eip1 = eip2;
				}
			if (count > 0) {
				rs = "0";// 执行成功
			}
		} finally {
			// 关闭连接
			inPst.close();
			db.closeCn();
		}
		return rs;
	}


	/*
	 * 查询EIP 销售订单表 zxjeip2cvbeln是否存在
	 * 传入参数字段均不能为空
	 */
	public boolean queryData_vbeln(String vbeln) throws SQLException {
		boolean bl = true;

		String sql = "select count(*) \"counts\" from zxj_eip_2cvbeln where no='" + vbeln + "'";
		ResultSet rs = null;
		PreparedStatement pst = null;
		pst = db.getConnetion().prepareStatement(sql);
		rs = pst.executeQuery();
		while(rs.next()) {
			if (rs.getInt("counts") > 0) {
				bl = false;
			}
		}
		pst.close();
		db.closeCn();

		return bl;
	}

	public boolean queryData_xsdd(String SONO) throws SQLException{
		boolean bl = true;

		String sql = "select count(*) \"counts\" from zxj_eip_xsdd where SONO='" + SONO + "'";
		ResultSet rs = null;
		PreparedStatement pst = null;
		pst = db.getConnetion().prepareStatement(sql);
		rs = pst.executeQuery();
		while(rs.next()) {
			if (rs.getInt("counts") > 0) {
				bl = false;
			}
		}
		pst.close();
		db.closeCn();

		return bl;
	}

	/*
	 * 判定是否为国网项目 根据采购合同编号 第5位和第11位或第6位和12位判定为G字母
	 */
	public boolean isSGxm_vbenln(String bstkd ,String khmc,String bstkde) {
		boolean bl = false;
		char c1 = bstkd.charAt(0);
			if ('A' <= c1 && c1 <= 'Z'&& bstkd.length()>11) {// 判断第一个字符是否为大写字母
				if (bstkd.charAt(5) == 'G' && bstkd.charAt(11) == 'G') {
					if(khmc.substring(0,2).equals("国网")) {
						if(!bstkde.trim().equals("")) {
							bl = true;
						}
					}
				}
			} else if(bstkd.length()>10){
				if (bstkd.charAt(4) == 'G' && bstkd.charAt(10) == 'G') {
					if(khmc.substring(0,2).equals("国网")) {
						if(!bstkde.trim().equals("")) {
							bl = true;
						}
					}
				}
			}
		return bl;
	}

	public boolean isSGxm_xsdd(String AESKD){
		if(StringUtils.isNotBlank(AESKD) && "监造".equals(AESKD)){
			return true;
		}
		return false;
	}
	/*
	 * String转换Timestamp
	 */
	public  Timestamp StringTotimeStamp(String dateString) {
		if (dateString.length() < 10) {
			dateString = dateString.substring(0, 4) + "-" + dateString.substring(4, 6) + "-"
					+ dateString.substring(6, 8);
		}
		StringBuffer ds = new StringBuffer(dateString);
		if (dateString.length() > 10) {
			Timestamp ts = Timestamp.valueOf(dateString);
			return ts;
		} else {
			ds.append(" 00:00:00");
			Timestamp ts = Timestamp.valueOf(ds.toString());
			return ts;
		}
	}

}
